在Oracle数据库（ORA

2023-06-19 13:42| 来源: 网络整理| 查看: 265

百度翻译此文有道翻译此文问题描述

I've read a lot of stuff about this error, and still not found the mistake.

I'm using JdbcTemplate to insert a row in some table with some timestamp column I'm pretty sure the timestamp is the problem, as if delete from the insert it works fine)

My code:

private static final String INSERT_CITAS = "INSERT INTO CITAS (" + "idCita, idServicio, " + "fechaCita, " + "idEstado, idUsuarioInicial) " + "VALUES (?, ?, ?, ?, ?)"; Object[] params = { idCita, citaQuenda.getIdServicio(), getDateToDBFormat(citaQuenda.getFechaCita()), ESTADO_INICIAL, USUARIO_INICIAL }; String queryCitas = INSERT_CITAS; super.getJdbcTemplate().update(queryCitas, params); protected String getDateToDBFormat(Date fechaCreacion){ return "TO_TIMESTAMP('" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(fechaCreacion) + "', 'yyyy-mm-dd hh24:mi:ss')" ; }

And having the next error:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO citas_55 (idCita, idServicio, fechaCita, idEstado, idUsuarioInicial) VALUES (?, ?, ?, ?, ?)]; ORA-01858: a non-numeric character was found where a numeric was expected

I've tried to execute the sql in some SQL editor having success, so I can't be more confused.

Being my params: [461, 100, TO_TIMESTAMP('2015-01-28 00:00:01', 'yyyy-mm-dd hh24:mi:ss'), 1, 8888] This actually works.

INSERT INTO citas (idCita, idServicio, fechaCita, idEstado, idUsuarioInicial) VALUES (457, 100, TO_TIMESTAMP('2015-01-28 00:00:01', 'yyyy-mm-dd hh24:mi:ss') , 1, 8888);

Any kind of help would be appreciated. Thanks in advance!

推荐答案

Don't convert back and forth between dates/timestamps and Strings.

Just pass a java.sql.Timestamp instance as a parameter:

Object[] params = { idCita, citaQuenda.getIdServicio(), new java.sql.Timestamp(citaQuenda.getFechaCita()), ESTADO_INICIAL, USUARIO_INICIAL }; String queryCitas = INSERT_CITAS; super.getJdbcTemplate().update(queryCitas, params); 其他推荐答案

I will go out on a limb here, and think I may see the problem. getDateToDBFormat() method is returning a String type, which contains the text, "TO_TIMESTAMP(...)". That is not a date or timestamp parameter. It is a string parameter. You need to do this instead:

Remove the TO_TIMESTAMP stuff from getDateToDBFormat() and have it just return the formatted DATE/TIME value (the format you show is not an oracle timestamp, but a DATE type).

change your insert to:

"INSERT INTO CITAS ... VALUES (?, ?, TO_DATE(?,?) , ?, ?)"

Where the parameters to the TO_DATE call are the return from getDateToDBFormat() and the second parameter is the date format mask. However, can't you just get rid of that mess and bind a Java Date type (or jdbc sql equivalent) directly?

That should work.

【本文地址】

公司简介

联系我们